I want to generate a 7 DIGIT identifier for primary key in Oracle 11g. The primary key must be 7 digits long.
Here is my code:
CREATE SEQUENCE sequence_staff
MINVALUE 1
START WITH 1
INCREMENT BY 1;
CREATE TABLE Staff (
Staff_ID INT PRIMARY KEY, --Autoincrement 7 digit --PK
Surname VARCHAR2(50) NOT NULL,
Firstnames VARCHAR2(50) NOT NULL,
Phone VARCHAR2(30) NOT NULL, --D1
Address VARCHAR2(150) NOT NULL
);
Currently the primary key is 1 (1 digit), not 0000001 (7 digits). Any ideas?
I_am_Batman :
You have to modify the sequence as follows :\n\nCREATE SEQUENCE sequence_staff\nMINVALUE 1000000\nSTART WITH 1000000\nINCREMENT BY 1 NOCACHE NOCYCLE;\n\n\nAlso, you have to insert the new staff_id column using sequence_staff.nextval always. See how it works hitting \n\nselect sequence_staff.nextval from dual; --repeated times.\n\n\nRead more about sequences here https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm\n\nEDIT :\n\nYes, it is possible. Create sequence the way you were creating and :\n\nselect to_char(sequence_staff.nextval,'FM0000000') from dual;\n\n\nEDIT 2 :\n\nThis link deserves the credit. \nhttp://stackoverflow.com/questions/14561210/creating-a-sequence-for-a-varchar2-field-in-oracle\n\nEDIT 3 : If you really want the results your way in Oracle Database you have to :\n\n1. alter table staff modify staff_id varchar(20);\n2. CREATE SEQUENCE sequence_staff\nMINVALUE 1\nSTART WITH 1\nINCREMENT BY 1 NOCACHE NOCYCLE;\n3. insert into staff(Staff_id, surname,firstnames, phone,address) values(to_char(sequence_staff.nextval,'FM0000000'),'Wayne','Bruce','0000','Gotham'); \n\n\n",
2016-04-17T10:03:59